CREATE TYPE authority AS ENUM ('user', 'mod', 'admin');

CREATE TABLE IF NOT EXISTS reports (
    int_id serial NOT NULL PRIMARY KEY,
    date_reported TIMESTAMP WITH TIME ZONE,
    date_autopurge TIMESTAMP WITH TIME ZONE,
    ip inet
);

CREATE TABLE IF NOT EXISTS images (
    int_id serial NOT NULL PRIMARY KEY,
    md5 text NOT NULL UNIQUE,
    size integer NOT NULL,
    full_x integer NOT NULL,
    full_y integer NOT NULL,
    thumb_x integer NOT NULL,
    thumb_y integer NOT NULL,
    thumb_file text,
    img_file text,
    exif text,
    report_id integer REFERENCES reports(int_id)
);

CREATE TABLE IF NOT EXISTS {{.}} (
    int_id SERIAL PRIMARY KEY,
    post_id integer NOT NULL,
    ghost_id integer,
    time TIMESTAMP WITH TIME ZONE NOT NULL,
    name text NOT NULL,
    trip text,
    subject text,
    ts_subject tsvector,
    mail text,
    poster_uid text,
    body text NOT NULL,
    ts_body tsvector,
    spoiler boolean DEFAULT false NOT NULL,
    post_deleted TIMESTAMP WITH TIME ZONE, 
    parent integer NOT NULL,
    image integer REFERENCES images(int_id),
    img_name text,
    img_url text,
    ip inet,
    passwd text,
    highlight boolean DEFAULT false NOT NULL,
    auth authority DEFAULT 'user' NOT NULL,
    report_id integer REFERENCES reports(int_id),
    UNIQUE (post_id, time)
);

CREATE INDEX {{.}}_parent_idx   ON {{.}}(parent);
CREATE INDEX {{.}}_post_id_idx  ON {{.}}(post_id);
CREATE INDEX {{.}}_name_idx     ON {{.}}(name);
CREATE INDEX {{.}}_img_name_idx ON {{.}}(img_name);
CREATE INDEX {{.}}_trip_idx     ON {{.}}(trip);
CREATE INDEX {{.}}_body_idx     ON {{.}} USING gin(ts_body);
CREATE INDEX {{.}}_subject_idx  ON {{.}} USING gin(ts_subject);

CREATE TABLE IF NOT EXISTS {{.}}_threads (
    op integer NOT NULL UNIQUE,
    sticky boolean DEFAULT false NOT NULL,
    locked boolean DEFAULT false NOT NULL,
    thread_deleted boolean DEFAULT false NOT NULL,
    last_updated TIMESTAMP WITH TIME ZONE NOT NULL,
    ghost_updated TIMESTAMP WITH TIME ZONE NOT NULL,
    all_posts integer NOT NULL,
    image_posts integer NOT NULL,
    expired TIMESTAMP WITH TIME ZONE,
    thread_url text NOT NULL
);
CREATE INDEX {{.}}_t_lu_idx ON {{.}}_threads(last_updated);

CREATE TABLE table_count(
        name TEXT PRIMARY KEY,
        rows BIGINT DEFAULT 0
);

--------------------------------------------------------------------
-- track row count to circumvent Postgres's O(n) COUNT(*).
--------------------------------------------------------------------
CREATE FUNCTION table_count_update() RETURNS TRIGGER AS $BODY$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE table_count SET rows = rows + 1 
            WHERE name = TG_TABLE_NAME;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE table_count SET rows = rows - 1 
            WHERE name = TG_TABLE_NAME;
    END IF;
    RETURN NULL;
END;
$BODY$ LANGUAGE 'plpgsql';

CREATE TRIGGER table_count_update AFTER INSERT OR DELETE ON {{.}}_threads
    FOR EACH ROW EXECUTE PROCEDURE table_count_update();

INSERT INTO table_count (name) VALUES ('{{.}}_threads');
--------------------------------------------------------------------
-- Mark thread as updated, increment all_posts.
-- If ghost_id is NOT NULL, replace arbitrary sentinel with correct
-- sequence value.
--------------------------------------------------------------------
CREATE FUNCTION {{.}}_post_added() RETURNS TRIGGER AS $$
BEGIN
    NEW.ts_body    := to_tsvector(NEW.body);
    NEW.ts_subject := to_tsvector(NEW.subject);
    UPDATE {{.}}_threads
        SET all_posts = all_posts + 1 
        WHERE op = NEW.parent;

    IF NEW.image IS NOT NULL THEN
        UPDATE {{.}}_threads 
            SET image_posts = image_posts + 1
        WHERE op = NEW.parent;
    END IF;

    IF NEW.ghost_id IS NOT NULL THEN
        SELECT max(post_id) INTO STRICT NEW.post_id
               FROM {{.}}
               WHERE parent = NEW.parent; 
        SELECT coalesce( max(ghost_id), 0) + 1 INTO STRICT NEW.ghost_id
               FROM {{.}}
               WHERE parent = NEW.parent AND
                     post_id = NEW.post_id AND
                     ghost_id IS NOT NULL;

        IF lower(NEW.mail) != 'sage' THEN
            UPDATE {{.}}_threads
                SET ghost_updated = NEW.time
                WHERE op = NEW.parent;
        END IF;
    ELSIF lower(NEW.mail) != 'sage' THEN
        UPDATE {{.}}_threads
            SET last_updated = NEW.time
            WHERE op = NEW.parent;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER {{.}}_post_added BEFORE INSERT ON {{.}}
    FOR EACH ROW EXECUTE PROCEDURE {{.}}_post_added();


CREATE FUNCTION {{.}}_report_post(id integer, addr inet, purge TIMESTAMP WITH TIME ZONE)
RETURNS void AS $$
DECLARE
    r_id integer;
    img_id integer;
BEGIN
    INSERT INTO reports (ip, date_reported, date_autopurge)
                VALUES (addr, NOW(), purge)
                RETURNING int_id
           INTO r_id;
    UPDATE {{.}} SET report_id = r_id WHERE int_id = id;
    SELECT image FROM {{.}} WHERE int_id = id INTO img_id;
    IF img_id IS NOT NULL THEN
        UPDATE images SET report_id = r_id WHERE int_id = img_id;
    END IF;
END;
$$ LANGUAGE plpgsql;

